package com.crm.dao;


import java.util.ArrayList;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Set;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;

import com.crm.entity.Opportunity;
import com.crm.entity.PartSources;
import com.crm.entity.Parts;
import com.crm.entity.SourceInfo;
import com.crm.entity.Sourcecompare;
import com.crm.entity.Sources;
import com.crm.util.ApplicationContext;
import com.crm.util.Constants;

public class SourceInfoDAO {
	protected final Log logger = LogFactory.getLog(getClass());

	public  void insert(SourceInfo ss)throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		Transaction tx=null;
		try {
			tx = session.beginTransaction();
			session.saveOrUpdate(ss);
			tx.commit();
			return ;
		}
		catch (Exception e) {
			if (tx!=null) tx.rollback();
			throw e;
		}
		finally {
			session.close();
		}
	}
	public  void insertList(List<SourceInfo> ss)throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		Transaction tx=null;
		try {
			tx = session.beginTransaction();
			for(SourceInfo s:ss){
					session.saveOrUpdate(s);
			}
			tx.commit();
			return ;
		}
		finally {
			session.close();
		}
	}

	
	

	
	public  Set<String> setSrc(int busid,String rfNo) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			System.out.println("SourceInfoDAO In setSrc");;
			Set<String> srcName=new HashSet<String>();
			String querystr="select distinct s.sourceName from Sources s where id in(select sourceid from PartSources  where enqid=(select id from Opportunity where ref_num=:erf and business_id=:busid) and is_src_completed=false) and business_id=:busid";
			Query qury=session.createQuery(querystr);
			qury.setString("erf", rfNo);
			qury.setInteger("busid", busid);
			List<Object> olst=(List<Object>)qury.list();
			for(Object s: olst){
				srcName.add((String)s);
			}
			return srcName;
		}
		catch (Exception e) {
			throw e;
		}
		finally {
			session.close();
		}
	}

	public  List<Parts> getPartsNo(String srname, String refNo,int busid) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			System.out.println("SourceInfoDAO In getPartsNo ");
			Set<Integer> ptnum=new HashSet<Integer>();
			List<Parts> lp=new ArrayList<Parts>();
			List<PartSources> lps=new ArrayList<PartSources>();
			String querystr="from PartSources where enqid=(select id from Opportunity where ref_num=:erf and business_id=:busid) and sourceid=(select id from Sources where sourcename=:sc and business_id=:busid) and business_id=:busid   order by partid ";
			Query qury=session.createQuery(querystr);
			qury.setString("erf", refNo);
			qury.setString("sc", srname);
			qury.setInteger("busid",busid);
			lps=(List<PartSources>)qury.list();
			Iterator<PartSources> itr=lps.iterator();
			while(itr.hasNext()==true){
				Object ob=itr.next();
				PartSources prtsrc=(PartSources)ob;
				Parts part=prtsrc.getPartid();
				lp.add(part);
			}
			return lp;
		}
		catch (Exception e) {
			throw e;
		}
		finally {
			session.close();
		}
	}

	public  List<SourceInfo> getAllPaartsNSuppliers(int enqid,int busid) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			String querystr = "from SourceInfo where enqid=:enqid and business_id=:busid";
			Query qury = session.createQuery(querystr);
			qury.setInteger("enqid", enqid);
			qury.setInteger("busid",busid);
			List<Object> olst = qury.list();
			
			List<SourceInfo> lst = new ArrayList<SourceInfo>();
			for(Object s: olst){
				lst.add((SourceInfo)s);
			}
			return lst;
		}
		catch (Exception e) {
			throw e;
		}
		finally {
			session.close();
		}
	}


	

	public  SourceInfo getSrInfo(int enqid, int partid, int sourceid, int supplierid,int busid) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			String querystr = "from SourceInfo where enqid=:enqid and prtnum=:prno and sourceid=:sno and suplierid=:spid and business_id=:busid";
			Query qury = session.createQuery(querystr);
			qury.setInteger("enqid", enqid);
			qury.setInteger("prno", partid);
			qury.setInteger("sno", sourceid);
			qury.setInteger("spid", supplierid);
			qury.setInteger("busid",busid);
			
			Object qy=qury.uniqueResult();
			SourceInfo si=(SourceInfo)qy;
			return si;
		}
		catch (Exception e) {
			throw e;
		}
		finally {
			session.close();
		}
	}
	public List<SourceInfo> getSrcDetails(int rfnum,int busid) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			String querystr = "from SourceInfo where business_id=:busid and enqid=:enqid ORDER BY prtnum";
			Query qury = session.createQuery(querystr);
			qury.setInteger("enqid", rfnum);
			qury.setInteger("busid",busid);
			List<SourceInfo> li=(List<SourceInfo>)qury.list();
			return li;
		}
		catch (Exception e) {
			throw e;
		}
		finally {
			session.close();
		}
	}



	public  void saveSrcCompr(Sourcecompare ss) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		Transaction tx=null;
		try {
				tx = session.beginTransaction();
			session.saveOrUpdate(ss);
			tx.commit();
			return ;
		}
		catch (Exception e) {
			if (tx!=null) tx.rollback();
			throw e;
		}
		finally {
			session.close();
		}
	}

	public  List<Parts> getPartsName(int rfNo, int sid,int busid) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			Set<Integer> ptnum=new HashSet<Integer>();
			List<Parts> lp=new ArrayList<Parts>();
			String qstr="from PartSources where enqid=:p and sourceid=:s  and business_id=:busid";
			Query q=session.createQuery(qstr);
			q.setInteger("p",rfNo);
			q.setInteger("s",sid);
			q.setInteger("busid",busid);
			List<Object> lo=q.list();
			Iterator itr=lo.iterator();
			while(itr.hasNext()==true){
				Object ob=itr.next();
				PartSources prtsrc=(PartSources)ob;
				Parts partobj=prtsrc.getPartid();
				lp.add(partobj);	
			}
			return lp;
		}
		catch (Exception e) {
			throw e;
		}
		finally {
			session.close();
		}
	}

	public  Sourcecompare getCmprId(int rfnum, int pr,int busid) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			String querystr = "from Sourcecompare where enqnum=:enqid and prtnum=:prid and business_id=:busid ";
			Query qury = session.createQuery(querystr);
			qury.setInteger("enqid", rfnum);
			qury.setInteger("prid",pr);
			qury.setInteger("busid",busid);
			Object ob=qury.uniqueResult();
			Sourcecompare s=(Sourcecompare)ob;
			return s;
		}
		catch (Exception e) {
			throw e;
		}
		finally {
			session.close();
		}
	}

	public  List<Object> getCmprRefNum(int busid) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			String stats=Constants.ST_GENERATED_QUOTATION;
			String querystr="from Opportunity   where business_id=:busid and status!=:status ORDER BY id DESC";
			Query query=session.createQuery(querystr);
			query.setString("status", stats);
			query.setInteger("busid",busid);
			List<Object> qresult=query.list();
			return qresult;
		}
		catch (Exception e) {
			throw e;
		}
		finally {
			session.close();
		}
	}
	
	public  void deleteSourceWithEnq(int enqid,int busid) throws Exception{
		Session session =  ApplicationContext.getHiberSession().openSession();
		Transaction tx=null;
		try {
				tx = session.beginTransaction();
			String qry="Delete from SourceInfo where enqid=:eqid and business_id=:busid ";
			Query query=session.createQuery(qry);
			query.setInteger("eqid", enqid);
			query.setInteger("busid",busid);
			query.executeUpdate();
			tx.commit();
		}
		catch (Exception e) {
		if (tx!=null) tx.rollback();
			throw e;
		}
		finally {
			session.close();
		}
	}

	public void savePartsSet(Set<PartSources> spart) throws Exception{
		Session session =  ApplicationContext.getHiberSession().openSession();
		Transaction tx=null;
		try {
			tx = session.beginTransaction();
			session.save(spart);
			tx.commit();
			return ;
		}
		catch (Exception e) {
		if (tx!=null) tx.rollback();
			throw e;
		}
		finally {
			session.close();
		}
	}
	
	public  SourceInfo getPartCurrency(int enqid,int partid,int supplierid,int busid) throws Exception{
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			SourceInfo sc=new SourceInfo();
			String qtr="from SourceInfo where business_id=:busid and enqid=:enqid and prtnum=:partid and suplierid=:supplierid and sourceid=:sourceid ";
			Query qry=session.createQuery(qtr);
			qry.setInteger("busid", busid);
			qry.setInteger("enqid", enqid);
			qry.setInteger("partid", partid);
			qry.setInteger("supplierid", supplierid);
			Object ob=qry.uniqueResult();
			sc=(SourceInfo) ob;
			return sc;
		}
		catch (Exception e) {
			throw e;
		}
		finally {
			session.close();
		}
	}
/*	public  List<Parts> getPartsNo(String srname, String refNo,int busid) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			System.out.println("SourceInfoDAO In getPartsNo ");
			Set<Integer> ptnum=new HashSet<Integer>();
			List<Parts> lp=new ArrayList<Parts>();
			String querystr="from PartSources where enqid=(select id from Opportunity where ref_num=:erf and business_id=:busid) and sourceid=(select id from Sources where sourcename=:sc and business_id=:busid) and business_id=:busid   order by partid ";
			Query qury=session.createQuery(querystr);
			qury.setString("erf", refNo);
			qury.setString("sc", srname);
			qury.setInteger("busid",busid);
			lp=(List<Parts>)qury.list();
			return lp;
		}
		catch (Exception e) {
			throw e;
		}
		finally {
			session.close();
		}
	}*/
}
